package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import java.util.WeakHashMap;

public class JDBCDemo13 {
    public static void main(String[] args) {
        try ( Connection conn = DBUtil.getConnection()){
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入要查看的老师的名字：");
            String t_name = sc.next();
            /*
            SELECT t.name AS "老师",t.salary 工资,t.title 职位,
             c.name AS "班级",c.floor 楼层,
              s.name AS "学生",s.age 年龄,s.gender 性别
            FROM teacher t
            JOIN class c ON t.id = c.teacher_id
            JOIN student s ON c.id = s.class_id
            WHERE t.name = ?
             */
            String sql = "SELECT t.name AS \"老师\",t.salary 工资,t.title 职位,\n" +
                    "             c.name AS \"班级\",c.floor 楼层,\n" +
                    "              s.name AS \"学生\",s.age 年龄,s.gender 性别\n" +
                    "            FROM teacher t\n" +
                    "            JOIN class c ON t.id = c.teacher_id\n" +
                    "            JOIN student s ON c.id = s.class_id\n" +
                    "            WHERE t.name = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1,t_name);
            ResultSet rs = statement.executeQuery();
            int i=0;
            while (rs.next()){
                String t_name1 = rs.getString("老师");
                int salary = rs.getInt("工资");
                String title = rs.getString("职位");
                String c_name = rs.getString("班级");
                int floor = rs.getInt("楼层");
                String s_name = rs.getString("学生");
                int age = rs.getInt("年龄");
                String gender = rs.getString("性别");
                if(i == 0){
                    System.out.println("老师："+t_name1+" -- 薪资："+salary+" -- 职位："+title+" 所带班级："+c_name+"  班级学生如下:");
                    System.out.println("=====================================================================");
                    System.out.println("班级："+c_name+" -- 楼层:"+floor);
                    System.out.println("=====================================================================");
                    System.out.println("学生姓名  -- 学生年龄  -- 学生性别 -- ");
                    i++;
                }
                if(s_name.length() == 2 )
                    System.out.println(s_name+"             "+age+"            "+gender);
                else if(s_name.length() == 3)
                    System.out.println(s_name+"           "+age+"           "+gender);
                else if (s_name.length() == 4) {
                    System.out.println(s_name+"          "+age+"           "+gender);
                }

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
